package org.anderground.app.storage.db;

import java.io.IOException;

import org.anderground.app.storage.db.DbStorage.CitiesTable;
import org.anderground.app.storage.db.DbStorage.LinesTable;
import org.anderground.app.storage.db.DbStorage.StationsTable;
import org.anderground.app.storage.db.DbStorage.TransfersTable;
import org.anderground.app.storage.db.DbStorage.TunnelsTable;
import org.anderground.core.Logger;
import org.anderground.core.model.IdBindingModelBuilder;
import org.anderground.core.model.ModelData;
import org.anderground.core.model.ModelException;
import org.anderground.core.storage.ModelReader;
import org.anderground.core.storage.StorageException;
import org.anderground.core.util.Coord;
import org.anderground.core.util.Time;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author Eldar Abusalimov
 */
public class DbModelReader extends AbstractDbReader<ModelData> implements
		ModelReader {

	private final long cityId;

	/**
	 * @param db
	 * @param cityId
	 */
	public DbModelReader(SQLiteDatabase db, long cityId) {
		super(db);
		this.cityId = cityId;
	}

	/**
	 * @param openHelper
	 * @param cityId
	 */
	public DbModelReader(SQLiteOpenHelper openHelper, long cityId) {
		super(openHelper);
		this.cityId = cityId;
	}

	/**
	 * @param db
	 * @throws IOException
	 */
	@Override
	protected ModelData readFromInput(SQLiteDatabase db) throws IOException {
		try {
			IdBindingModelBuilder builder = new IdBindingModelBuilder();
			long mapRowId = selectMap(db, cityId, builder);
			if (mapRowId <= 0) {
				throw new StorageException(
						"there is no map row for city with id " + cityId);
			}

			selectStations(db, mapRowId, builder);
			selectLines(db, mapRowId, builder);
			selectTransfers(db, mapRowId, builder);
			selectTunnels(db, mapRowId, builder);

			return builder.build();
		} catch (ModelException e) {
			Logger.e("unexpected situation", e);
			throw new StorageException(
					"unexpected ModelException during the reading", e);
		}
	}

	/**
	 * @param db
	 * @param cityId
	 * @param builder
	 * @return the row ID, or -1 if an error occurred
	 */
	private static long selectMap(SQLiteDatabase db, long cityId,
			IdBindingModelBuilder builder) {
		final String tCities = CitiesTable.NAME;
		final String tCities_Id = CitiesTable.Columns._ID;

		Cursor cursor = db.rawQuery("SELECT * FROM " + tCities + " WHERE "
				+ tCities_Id + " = " + cityId, null);
		try {
			int tCities_MapId_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns.MAP_ID);
			final int tCities_Name_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns.NAME);
			final int tCities_Latitude_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns.LATITUDE);
			final int tCities_Longitude_Index = cursor
					.getColumnIndexOrThrow(CitiesTable.Columns.LONGITUDE);
			if (cursor.moveToFirst()) {
				String name = cursor.getString(tCities_Name_Index);
				int latitude = cursor.getInt(tCities_Latitude_Index);
				int longitude = cursor.getInt(tCities_Longitude_Index);
				Coord location = new Coord(latitude, longitude);
				// TODO
				builder.setCity(name, null, null, null, location);
				return cursor.getLong(tCities_MapId_Index);
			} else {
				return -1;
			}
		} finally {
			cursor.close();
		}
	}

	/**
	 * 
	 * @param db
	 * @param mapRowId
	 * @param builder
	 * @throws ModelException
	 */
	private static void selectStations(SQLiteDatabase db, long mapRowId,
			IdBindingModelBuilder builder) throws ModelException {

		final String tStations = StationsTable.NAME;
		final String tStations_MapId = StationsTable.Columns.MAP_ID;

		Cursor cursor = db.rawQuery("SELECT * FROM " + tStations + " WHERE "
				+ tStations_MapId + " = " + mapRowId, null);
		try {
			final int tStations_Id_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns._ID);
			final int tStations_Name_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.NAME);
			final int tStations_Open_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.OPEN);
			final int tStations_Close_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.CLOSE);
			final int tStations_Latitude_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.LATITUDE);
			final int tStations_Longitude_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.LONGITUDE);
			final int tStations_PositionX_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.POSITION_X);
			final int tStations_PositionY_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.POSITION_Y);
			final int tStations_LabelDx_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.LABEL_DX);
			final int tStations_LabelDy_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.LABEL_DY);
			final int tStations_LabelAngle_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.LABEL_ANGLE);
			final int tStations_LabelVisible_Col = cursor
					.getColumnIndexOrThrow(StationsTable.Columns.LABEL_VISIBLE);

			if (cursor.moveToFirst()) {
				do {
					int id = cursor.getInt(tStations_Id_Col);
					String name = cursor.getString(tStations_Name_Col);
					Time open = new Time(cursor.getInt(tStations_Open_Col));
					Time close = new Time(cursor.getInt(tStations_Close_Col));
					int latitude = cursor.getInt(tStations_Latitude_Col);
					int longitude = cursor.getInt(tStations_Longitude_Col);
					int positionX = cursor.getInt(tStations_PositionX_Col);
					int positionY = cursor.getInt(tStations_PositionY_Col);
					int labelX = cursor.getInt(tStations_LabelDx_Col);
					int labelY = cursor.getInt(tStations_LabelDy_Col);
					int labelAngle = cursor.getInt(tStations_LabelAngle_Col);
					boolean labelVisible = cursor
							.getInt(tStations_LabelVisible_Col) > 0;
					Coord position = new Coord(positionX, positionY);
					Coord location = new Coord(latitude, longitude);
					builder.addStation(id, name, position, location, open,
							close, labelX, labelY, labelAngle, labelVisible);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}

	}

	/**
	 * @param db
	 * @param mapRowId
	 * @param builder
	 * @throws ModelException
	 */
	private static void selectLines(SQLiteDatabase db, long mapRowId,
			IdBindingModelBuilder builder) throws ModelException {

		final String tLines = LinesTable.NAME;
		final String tLines_MapId = LinesTable.Columns.MAP_ID;

		Cursor cursor = db.rawQuery("SELECT * FROM " + tLines + " WHERE "
				+ tLines_MapId + " = " + mapRowId, null);
		try {
			final int tLines_Id_Col = cursor
					.getColumnIndexOrThrow(LinesTable.Columns._ID);
			final int tLines_Name_Col = cursor
					.getColumnIndexOrThrow(LinesTable.Columns.NAME);
			final int tLines_Color_Col = cursor
					.getColumnIndexOrThrow(LinesTable.Columns.COLOR);
			final int tLines_TrainDelay_Col = cursor
					.getColumnIndexOrThrow(LinesTable.Columns.TRAIN_DELAY);

			if (cursor.moveToFirst()) {
				do {
					int id = cursor.getInt(tLines_Id_Col);
					String name = cursor.getString(tLines_Name_Col);
					int color = cursor.getInt(tLines_Color_Col);
					int trainDelay = cursor.getInt(tLines_TrainDelay_Col);
					builder.addLine(id, name, color, trainDelay);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}

	}

	/**
	 * @param db
	 * @param mapRowId
	 * @param builder
	 * @throws ModelException
	 */
	private static void selectTransfers(SQLiteDatabase db, long mapRowId,
			IdBindingModelBuilder builder) throws ModelException {

		final String tStations = StationsTable.NAME;
		final String tStations_Id = fullFieldName(tStations,
				StationsTable.Columns._ID);
		final String tStations_MapId = fullFieldName(tStations,
				StationsTable.Columns.MAP_ID);

		final String tTransfers = TransfersTable.NAME;
		final String tTransfers_Time = TransfersTable.Columns.TIME;
		final String tTransfers_FromStationId = TransfersTable.Columns.FROM_STATION_ID;
		final String tTransfers_ToStationId = TransfersTable.Columns.TO_STATION_ID;

		Cursor cursor = db.rawQuery("SELECT " + tTransfers_Time + ", "
				+ tTransfers_ToStationId + ", " + tTransfers_FromStationId
				+ " " + " FROM " + tTransfers + " INNER JOIN " + tStations
				+ " AS " + tStations + " ON " + tTransfers_FromStationId
				+ " = " + tStations_Id + " WHERE " + tStations_MapId + " = "
				+ mapRowId, null);
		try {
			final int tTransfers_Time_Col = cursor
					.getColumnIndexOrThrow(tTransfers_Time);
			final int tTransfers_FromStationId_Col = cursor
					.getColumnIndexOrThrow(tTransfers_FromStationId);
			final int tTransfers_ToStationId_Col = cursor
					.getColumnIndexOrThrow(tTransfers_ToStationId);

			if (cursor.moveToFirst()) {
				do {
					int from = cursor.getInt(tTransfers_FromStationId_Col);
					int to = cursor.getInt(tTransfers_ToStationId_Col);
					int time = cursor.getInt(tTransfers_Time_Col);
					builder.addTransfer(to, from, time);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}

	}

	/**
	 * @param db
	 * @param mapRowId
	 * @param builder
	 * @throws ModelException
	 */
	private static void selectTunnels(SQLiteDatabase db, long mapRowId,
			IdBindingModelBuilder builder) throws ModelException {

		final String tStations = StationsTable.NAME;
		final String tStations_Id = fullFieldName(tStations,
				StationsTable.Columns._ID);
		final String tStations_MapId = fullFieldName(tStations,
				StationsTable.Columns.MAP_ID);

		final String tTunnels = TunnelsTable.NAME;
		final String tTunnels_Time = TunnelsTable.Columns.TIME;
		final String tTunnels_ToStationId = TunnelsTable.Columns.TO_STATION_ID;
		final String tTunnels_FromStationId = TunnelsTable.Columns.FROM_STATION_ID;
		final String tTunnels_LineId = TunnelsTable.Columns.LINE_ID;
		final String tTunnels_Smooth = TunnelsTable.Columns.SMOOTH;

		Cursor cursor = db.rawQuery("SELECT " + tTunnels_Time + ", "
				+ tTunnels_ToStationId + ", " + tTunnels_FromStationId + ", "
				+ tTunnels_Smooth + ", " + tTunnels_LineId + " " + " FROM "
				+ tTunnels + " INNER JOIN " + tStations + " AS " + tStations
				+ " ON " + tTunnels_FromStationId + " = " + tStations_Id
				+ " WHERE " + tStations_MapId + " = " + mapRowId, null);
		try {
			final int tTunnels_Time_Col = cursor
					.getColumnIndexOrThrow(tTunnels_Time);
			final int tTunnels_ToStationId_Col = cursor
					.getColumnIndexOrThrow(tTunnels_ToStationId);
			final int tTunnels_FromStationId_Col = cursor
					.getColumnIndexOrThrow(tTunnels_FromStationId);
			final int tTunnels_LineId_Col = cursor
					.getColumnIndexOrThrow(tTunnels_LineId);
			final int tTunnels_Smooth_Col = cursor
					.getColumnIndexOrThrow(tTunnels_Smooth);

			if (cursor.moveToFirst()) {
				do {
					int from = cursor.getInt(tTunnels_FromStationId_Col);
					int to = cursor.getInt(tTunnels_ToStationId_Col);
					int time = cursor.getInt(tTunnels_Time_Col);
					int line = cursor.getInt(tTunnels_LineId_Col);
					boolean smooth = cursor.getInt(tTunnels_Smooth_Col) > 0;
					builder.addTunnel(from, to, time, line, smooth);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}

	}

	private static String fullFieldName(String table, String field) {
		return table + "." + field;
	}

}
